package com.tdam2014.grupo02;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

	static final String dbName="flickrDB";
	static final String imageTable="Images";
	static final String colImageID="ImageID";
	static final String colImageName="ImageName";
	static final String colImageDate="ImageDate";
	static final String colImageFolderID="FolderID";
	
	static final String folderTable="Folders";
	static final String colFolderID="FolderID";
	static final String colFolderName="FolderName";
	
	static final String commentTable="Comments";
	static final String colCommentID="CommentID";
	static final String colCommentMessage="CommentMessage";
	static final String colCommentDate="CommentDate";
	static final String colCommentUser="CommentUser";
	static final String colCommentImageID="ImageID";
	
	static final String viewEmps="ViewEmps";
	
	
	public DatabaseHelper(Context context) {
		super(context, dbName, null,1);
		
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
		db.execSQL("CREATE TABLE "+folderTable+" ("+colFolderID+ " INTEGER PRIMARY KEY AUTOINCREMENT, "+
				colFolderName+ " TEXT)");
		
		db.execSQL("CREATE TABLE "+imageTable+" ("+colImageID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
				colImageName+" TEXT, "+colImageDate+" DATE, "+colImageFolderID+" INTEGER NOT NULL ,FOREIGN KEY ("+colFolderID+") REFERENCES "+folderTable+" ("+colFolderID+"));");
		
		db.execSQL("CREATE TABLE "+commentTable+" ("+colCommentID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
				colCommentMessage+" TEXT, "+colCommentDate+" DATE, "+colCommentUser+" TEXT, "+colCommentImageID+" INTEGER NOT NULL ,FOREIGN KEY ("+colCommentImageID+") REFERENCES "+imageTable+" ("+colImageID+"));");
		
		db.execSQL("CREATE TRIGGER fk_imageId_folderId " +
				" BEFORE INSERT "+
				" ON "+imageTable+
				
				" FOR EACH ROW BEGIN"+
				" SELECT CASE WHEN ((SELECT "+colFolderID+" FROM "+folderTable+" WHERE "+colFolderID+"=new."+colFolderID+" ) IS NULL)"+
				" THEN RAISE (ABORT,'Foreign Key Violation') END;"+
				"  END;");
		
		db.execSQL("CREATE TRIGGER fk_commentId_imageId " +
				" BEFORE INSERT "+
				" ON "+commentTable+
				
				" FOR EACH ROW BEGIN"+
				" SELECT CASE WHEN ((SELECT "+colImageID+" FROM "+imageTable+" WHERE "+colImageID+"=new."+colImageID+" ) IS NULL)"+
				" THEN RAISE (ABORT,'Foreign Key Violation') END;"+
				"  END;");
		
		//Inserts pre-defined departments
		//InsertDepts(db);
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
		db.execSQL("DROP TABLE IF EXISTS "+commentTable);
		db.execSQL("DROP TABLE IF EXISTS "+imageTable);
		db.execSQL("DROP TABLE IF EXISTS "+folderTable);
		
		
		db.execSQL("DROP TRIGGER IF EXISTS fk_imageId_folderId");
		db.execSQL("DROP TRIGGER IF EXISTS fk_commentId_imageId");

		onCreate(db);
	}
	
//	 void AddEmployee(Employee emp)
//	{
	//		 SQLiteDatabase db= this.getWritableDatabase();
	//		
	//	ContentValues cv=new ContentValues();
	//	
	//	cv.put(colName, emp.getName());
	//	cv.put(colAge, emp.getAge());
	//	cv.put(colDept, emp.getDept());
	//	//cv.put(colDept,2);
		
	//	db.insert(employeeTable, colName, cv);
	//	db.close();
		
		
	//	}
	 
	// int getEmployeeCount()
	// {
	//	SQLiteDatabase db=this.getWritableDatabase();
	//	Cursor cur= db.rawQuery("Select * from "+employeeTable, null);
	//	int x= cur.getCount();
	//	cur.close();
	//	return x;
	// }
	 
	// Cursor getAllEmployees()
	// {
	//	 SQLiteDatabase db=this.getWritableDatabase();
		 
		 
		 
		 //Cursor cur= db.rawQuery("Select "+colID+" as _id , "+colName+", "+colAge+" from "+employeeTable, new String [] {});
	//	 Cursor cur= db.rawQuery("SELECT * FROM "+viewEmps,null);
	//	 return cur;
		 
	// }
	 
	// Cursor getAllDepts()
	// {
	//	 SQLiteDatabase db=this.getReadableDatabase();
	//	 Cursor cur=db.rawQuery("SELECT "+colDeptID+" as _id, "+colDeptName+" from "+deptTable,new String [] {});
		 
	//	 return cur;
	// }
	 
	// void InsertDepts(SQLiteDatabase db)
	// {
	//	 ContentValues cv=new ContentValues();
	//		cv.put(colDeptID, 1);
	//		cv.put(colDeptName, "Sales");
	//		db.insert(deptTable, colDeptID, cv);
	//		cv.put(colDeptID, 2);
	//		cv.put(colDeptName, "IT");
	//		db.insert(deptTable, colDeptID, cv);
	//		cv.put(colDeptID, 3);
	//		cv.put(colDeptName, "HR");
	//		db.insert(deptTable, colDeptID, cv);
	//		db.insert(deptTable, colDeptID, cv);
			
	// }
	 
	// public String GetDept(int ID)
	// {
	//	 SQLiteDatabase db=this.getReadableDatabase();
		 
	//	 String[] params=new String[]{String.valueOf(ID)};
	//	 Cursor c=db.rawQuery("SELECT "+colDeptName+" FROM"+ deptTable+" WHERE "+colDeptID+"=?",params);
	//	 c.moveToFirst();
	//	 int index= c.getColumnIndex(colDeptName);
	//	 return c.getString(index);
	// }
	 
	// public Cursor getEmpByDept(String Dept)
	// {
	//	 SQLiteDatabase db=this.getReadableDatabase();
	//	 String [] columns=new String[]{"_id",colName,colAge,colDeptName};
	//	 Cursor c=db.query(viewEmps, columns, colDeptName+"=?", new String[]{Dept}, null, null, null);
	//	 return c;
	// }
	 
	// public int GetDeptID(String Dept)
	// {
	//	 SQLiteDatabase db=this.getReadableDatabase();
	//	 Cursor c=db.query(deptTable, new String[]{colDeptID+" as _id",colDeptName},colDeptName+"=?", new String[]{Dept}, null, null, null);
	//	Cursor c=db.rawQuery("SELECT "+colDeptID+" as _id FROM "+deptTable+" WHERE "+colDeptName+"=?", new String []{Dept});
	//	 c.moveToFirst();
	//	 return c.getInt(c.getColumnIndex("_id"));
		 
	//	 }
	 
//	 public int UpdateEmp(Employee emp)
//	 {
//		 SQLiteDatabase db=this.getWritableDatabase();
//		 ContentValues cv=new ContentValues();
//		 cv.put(colName, emp.getName());
//		 cv.put(colAge, emp.getAge());
//		 cv.put(colDept, emp.getDept());
//		 return db.update(employeeTable, cv, colID+"=?", new String []{String.valueOf(emp.getID())});
//	 }
	 
//	 public void DeleteEmp(Employee emp)
//	 {
//		 SQLiteDatabase db=this.getWritableDatabase();
//		 db.delete(employeeTable,colID+"=?", new String [] {String.valueOf(emp.getID())});
//		 db.close();
//	 }

}
